﻿// ----------------------------------------------------------------------
// <author name="Moses Kehengu"/>
// <copyright file="GenericDataAccess.cs" company="Futures Group International">
//     Copyright statement. All right reserved
// </copyright>
//
// ------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Configuration;
using DataModels.Common;

namespace GenericTools
{
    /// <summary>
    /// Contains static methods to access data from any SQL Server
    /// </summary>
    public static class GenericDataAccess
    {
        #region Create Command
        /// <summary>
        /// Creates and prepares a new DbCommand object on a new connection
        /// </summary>
        /// <returns></returns>
        public static DbCommand CreateCommand()
        {
            string dataProviderName = AppConfiguration.DBProviderName;
            string connectionString = AppConfiguration.DBConnectionString;

            DbCommand command = CreateCommand(dataProviderName, connectionString);
            return command;
        }

        /// <summary>
        /// Creates and prepares a new DbCommand object on a new connection
        /// </summary>
        /// <param name="dataProviderName">Data Provider</param>
        /// <param name="connectionString">Connection String</param>
        /// <returns>DbCommand object</returns>
        public static DbCommand CreateCommand(string dataProviderName, string connectionString)
        {

            DbProviderFactory factory = DbProviderFactories.GetFactory(dataProviderName);
            DbConnection connection = factory.CreateConnection();
            connection.ConnectionString = connectionString;

            DbCommand command = connection.CreateCommand();
            return command;
        }

        /// <summary>
        /// Creates and prepares a new DbCommand object for cache database
        /// </summary>
        /// <returns></returns>
        public static DbCommand CreateCacheDBCommand()
        {
            string dataProviderName = AppConfiguration.CacheDBProviderName;
            string connectionString = AppConfiguration.CacheDBConnectionString;

            DbCommand command = CreateCommand(dataProviderName, connectionString);
            return command;
        }
        #endregion

        #region Executing Command
        /// <summary>
        /// Executes a select command and returns the results as a DataTable object
        /// </summary>
        /// <param name="command">Select Command</param>
        /// <returns></returns>
        public static DataTable ExecuteSelectCommand(DbCommand command, bool closeConnection = true)
        {
            DataTable table;
            try
            {
                if (command.Connection.State != ConnectionState.Open) command.Connection.Open();
                DbDataReader reader = command.ExecuteReader();
                table = new DataTable();
                table.Load(reader);

                reader.Close();
            }
            catch (Exception ex)
            {
                Utilities.LogError(ex, command);
                throw;
            }
            finally
            {
                if (closeConnection) command.Connection.Close();
            }
            return table;
        }

        /// <summary>
        /// Executes an update, delete, or insert command and returns the number of affected rows
        /// </summary>
        /// <param name="command">Command to execute</param>
        /// <returns>Number of affected rows</returns>
        public static int ExecuteNonQuery(DbCommand command, bool closeConnection = true)
        {
            int affectedRows = -1;
            try
            {
                if (command.Connection.State != ConnectionState.Open) command.Connection.Open();
                affectedRows = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Utilities.LogError(ex, command);
                throw;
            }
            finally
            {
                if (closeConnection) command.Connection.Close();
            }
            return affectedRows;
        }

        /// <summary>
        /// Executes a select command and return a single result as a string
        /// </summary>
        /// <param name="command">Select command</param>
        /// <returns></returns>
        public static string ExecuteScalar(DbCommand command, bool closeConnection = true)
        {
            string value = "";
            try
            {
                if (command.Connection.State != ConnectionState.Open) command.Connection.Open();
                object output = command.ExecuteScalar();
                if (output != null) value = output.ToString();
            }
            catch (Exception ex)
            {
                Utilities.LogError(ex, command);
                throw;
            }
            finally
            {
                if (closeConnection) command.Connection.Close();
            }
            return value;
        }
        #endregion

        #region Executing Query
        /// <summary>
        /// Runs specified query and return the results in the DataTable object
        /// </summary>
        /// <param name="query">SQL statement</param>
        /// <returns></returns>
        public static DataTable RunSelectQuery(string query)
        {
            DbCommand command = GenericDataAccess.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = query;
            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            return dataTable;
        }

        private static int RunDeleteInsertUpdateQuery(string query)
        {
            DbCommand command = GenericDataAccess.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = query;
            int affectedRows = GenericDataAccess.ExecuteNonQuery(command);
            return affectedRows;
        }

        public static int RunDeleteQuery(string query)
        {
            return RunDeleteInsertUpdateQuery(query);
        }

        public static int RunInsertQuery(string query)
        {
            return RunDeleteInsertUpdateQuery(query);
        }

        public static int RunUpdateQuery(string query)
        {
            return RunDeleteInsertUpdateQuery(query);
        }
        #endregion
    }
}
